package org.mebusw.dao.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.mebusw.dao.IGameTableDAO;
import org.mebusw.struts.davinci.bean.GameBrief;
import org.mebusw.struts.vo.GameTable;

/**
 * mysql> desc games; +------------+---------+------+-----+---------+ | Field |
 * Type | Null | Key | Default | +------------+---------+------+-----+---------+
 * | gameTable | text | YES | | NULL | | createDate | date | YES | | NULL | |
 * data | blob | YES | | NULL | | gid | int(11) | NO | PRI | NULL |
 * +------------+---------+------+-----+---------+
 * 
 * @author j1shen
 * 
 */
public class IGameTableDAOImpl implements IGameTableDAO {
	private Connection conn = null;

	public IGameTableDAOImpl(Connection conn) {
		this.conn = conn;
	}

	public boolean doCreate(GameTable gameTable) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql;
		int currentMaxGid = 0;

		try {
			sql = "SELECT MAX(gid) from games";
			pstmt = this.conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				currentMaxGid = rs.getInt(1);
				gameTable.setGid(currentMaxGid + 1);
			}

			sql = "INSERT INTO games(data, gid, gameDesc, playerCount, gameStatus) VALUES (?,?,?,?,?)";
			pstmt = this.conn.prepareStatement(sql);

			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(baos);
			oos.writeObject(gameTable);
			ByteArrayInputStream bais = new ByteArrayInputStream(baos
					.toByteArray());

			pstmt.setBinaryStream(1, bais, bais.available());
			oos.close();
			pstmt.setInt(2, gameTable.getGid());
			pstmt.setString(3, gameTable.getDescription());
			pstmt.setInt(4, gameTable.getPlayerCount());
			pstmt.setString(5, gameTable.getGameStatus().toString()
					+ gameTable.getPlayers()[gameTable.getCurrPlayer()]);

			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}
		return flag;
	}

	@Override
	public boolean doDelete(int gid) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM games WHERE gid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, gid);
			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}

		return flag;
	}

	@Override
	public boolean doUpdate(GameTable gameTable) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "UPDATE games SET data=?, gameStatus=?, playerCount=? WHERE gid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(baos);
			oos.writeObject(gameTable);
			ByteArrayInputStream bais = new ByteArrayInputStream(baos
					.toByteArray());
			pstmt.setBinaryStream(1, bais, bais.available());
			oos.close();
			pstmt.setString(2, gameTable.getGameStatus().toString()
					+ gameTable.getPlayers()[gameTable.getCurrPlayer()]);
			pstmt.setInt(3, gameTable.getPlayerCount());
			pstmt.setInt(4, gameTable.getGid());

			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}

		return flag;
	}

	@Override
	public GameTable findById(int gid) throws Exception {
		GameTable gameTable = null;
		PreparedStatement pstmt = null;
		String sql = "SELECT data FROM games WHERE gid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, gid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {

				ObjectInputStream ois = new ObjectInputStream(rs
						.getBinaryStream(1));
				gameTable = (GameTable) ois.readObject();
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return gameTable;
	}

	@Override
	public int getAllCount(String keyword) throws Exception {
		PreparedStatement pstmt = null;
		String sql;
		int count = 0;

		try {
			sql = "select COUNT(uid) from user "
					+ "where uid like upper(?) or username like upper(?) or score like upper(?) ";

			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, "%" + keyword + "%");
			pstmt.setString(2, "%" + keyword + "%");
			pstmt.setString(3, "%" + keyword + "%");

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return count;
	}

	public List<GameBrief> findAll() throws Exception {
		List<GameBrief> allGame = new ArrayList<GameBrief>();
		GameBrief gb = null;
		PreparedStatement pstmt = null;
		String sql;

		try {
			// for MySQL statement
			sql = "SET @cnt = 0";
			pstmt = this.conn.prepareStatement(sql);
			pstmt.executeQuery();

			sql = "select * from ("
					+ "select (@cnt := @cnt + 1) as rn, gid, gameDesc, gameStatus, playerCount from games) as temp";

			pstmt = this.conn.prepareStatement(sql);

			ResultSet rs = pstmt.executeQuery();
			// int rn;
			while (rs.next()) {
				// rn = rs.getInt(1);
				gb = new GameBrief();
				gb.setGid(rs.getInt(2));
				gb.setDescription(rs.getString(3));
				gb.setGameStatus(rs.getString(4));
				gb.setPlayerCount(rs.getInt(5));
				//TODO more info should be added to gameBrief
				allGame.add(gb);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return allGame;
	}

	public int getAllCount() throws Exception {
		PreparedStatement pstmt = null;
		String sql;
		int count = 0;

		try {
			sql = "select COUNT(gid) from games ";
			pstmt = this.conn.prepareStatement(sql);

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return count;
	}

}
